﻿Imports Mysoft.Map.Data
Imports Mysoft.Map.Excel

Partial Public Class MyDataExport
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim strType, strInfo As String
        Dim strReturn As String = ""

        strType = Request.QueryString("p_businessType")     '业务标识
        strInfo = Request.QueryString("p_businessInfo")     '业务信息

        Dim buf(Request.InputStream.Length - 1) As Byte
        Request.InputStream.Read(buf, 0, Request.InputStream.Length)

        Dim strPostData As String = System.Text.Encoding.UTF8.GetString(buf) '提交数据

        Select Case strType.ToUpper
            Case "ExcelBySql".ToUpper
                Dim strTitle As String = Request.QueryString("title")
                Dim strFileName As String = Request.QueryString("file")
                If getExcelBySql(strPostData, strTitle, strFileName) Then
                    strReturn = "true"
                Else
                    strReturn = "false"
                End If
                Exit Select
        End Select
        Response.ContentType = "text/HTML"
        Response.Clear()
        Response.Write(strReturn)
        Response.End()


    End Sub


    '校验SQL语句中不能包含Insert、Update、Delete、Create等语句
    Private Function VerifySql(ByVal strSql As String) As Boolean
        strSql = strSql.ToLower

        If strSql.IndexOf("create ") >= 0 Then Return False
        If strSql.IndexOf("insert ") >= 0 Then Return False
        If strSql.IndexOf("update ") >= 0 Then Return False
        If strSql.IndexOf("delete ") >= 0 Then Return False

        Return True
    End Function


    '通过传入的SQL查找数据，生成以FileName参数为名的Excel文件
    '生成成功返回True，失败返回False
    Public Function getExcelBySql(ByVal strSql As String, ByVal strTitle As String, ByVal strFileName As String) As Boolean
        'SQL、标题、文件名都不能为空
        If String.IsNullOrEmpty(strSql) Or String.IsNullOrEmpty(strTitle) Or String.IsNullOrEmpty(strFileName) Then
            Return False
        End If

        If Not VerifySql(strSql) Then Return False
        Dim dt As DataTable = MyDB.GetDataTable(strSql)
        If dt.Rows.Count = 0 Then Return False '如果没有数据则返回失败

        Dim ds As DataSet = New DataSet()
        dt.TableName = strTitle
        ds.Tables.Add(dt.Copy())

        Return getExcelByData(ds, strFileName)
    End Function

    '通过传入的DataSet生成以FileName参数为名的Excel文件
    '生成成功返回True，失败返回False
    Public Function getExcelByData(ByVal dats As DataSet, ByVal strFileName As String) As Boolean
        '数据表、文件名不能为空
        If dats.Tables.Count = 0 Or String.IsNullOrEmpty(strFileName) Then
            Return False
        End If

        '生成一个Excel工作表对象
        Dim wb As ExcelWookbook = getExcelByData(dats)

        '保存Excel对象至物理路径
        Dim phyFileName As String = Server.MapPath(strFileName)

        Try
            wb.SaveXls(phyFileName)
        Catch ex As Exception
            'Throw New Exception("创建Excel文件时发生异常: " & ex.Message)
            Return False
        End Try

        Return True
    End Function

    '通过传入的DataSet生成Excel对象
    Private Function getExcelByData(ByVal dats As DataSet) As ExcelWookbook
        Dim wb As ExcelWookbook = New ExcelWookbook
        Try
            '将数据集中的每个表生成工作区数据
            For Each dt As DataTable In dats.Tables
                '增加一个工作区，以DataSet中的TableName命名
                Dim ws As ExcelWorksheet = wb.Worksheets.Add(dt.TableName)
                '显示标题 
                Dim strHead As String
                Dim headIndex As Integer
                Dim headLength As Integer = dt.Columns.Count
                For headIndex = 0 To headLength - 1
                    strHead = dt.Columns(headIndex).ColumnName
                    ws.Cells(0, headIndex).Value = strHead
                Next

                '插入数据行
                Dim rowCount As Integer = dt.Rows.Count
                Dim rowIndex As Integer
                Dim colIndex As Integer
                Dim dataCell As ExcelCell
                Dim dr As DataRow
                For rowIndex = 0 To rowCount - 1
                    dr = dt.Rows(rowIndex)

                    For colIndex = 0 To headLength - 1
                        dataCell = ws.Cells(rowIndex + 1, colIndex)
                        If dt.Columns(colIndex).DataType Is System.Type.GetType("System.Guid") Then
                            dataCell.Value = dr(colIndex).ToString
                        Else
                            dataCell.Value = dr(colIndex)
                        End If
                    Next
                Next
            Next
        Catch ex As Exception

        End Try
        Return wb
    End Function



End Class